﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
namespace Paros.DAL
{
    public class DBFactory
    {
        readonly string ProviderName = ConfigurationManager.AppSettings["DbProviderName"];
        readonly string ConnectionString = ConfigurationManager.AppSettings["DbConnectionString"];
        DbProviderFactory instance;
        public DBFactory()
        {
            instance = DbProviderFactories.GetFactory(ProviderName);
        }

        public static DbProviderFactory Instance
        {
            get
            {
                return DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["DbProviderName"]);
            }
        }

        private DbConnection CreateConnection()
        {
            DbConnection conn = instance.CreateConnection();
            conn.ConnectionString = ConnectionString;
            return conn;

        }

        public DataSet ExecuteDataSet(string cmdText, DbParameter[] paramCollection)
        {
            DataSet ds = null;
            using (DbConnection conn = CreateConnection())
            {
                conn.Open();
                DbCommand cmd = instance.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                if(paramCollection != null)
                {
                    foreach(DbParameter pa in paramCollection)
                    {
                        cmd.Parameters.Add(pa);
                    }
                }

                DbDataAdapter adapter = instance.CreateDataAdapter();
                adapter.SelectCommand = cmd;

                ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();     
            }
            return ds;
        }

        public int ExecuteNonQuery(string cmdText, DbParameter[] paramCollection)
        {
            int res = -1;
            using (DbConnection conn = CreateConnection())
            {
                conn.Open();
                DbCommand cmd = instance.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                if (paramCollection != null)
                {
                    foreach (DbParameter pa in paramCollection)
                    {
                        cmd.Parameters.Add(pa);
                    }
                }

                res = cmd.ExecuteNonQuery();
                conn.Close();
            }

            return res;
        }

        public object ExecuteScalar(string cmdText, DbParameter[] paramCollection)
        {
            object res = DBNull.Value;
            using (DbConnection conn = CreateConnection())
            {
                conn.Open();
                DbCommand cmd = instance.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                if (paramCollection != null)
                {
                    foreach (DbParameter pa in paramCollection)
                    {
                        cmd.Parameters.Add(pa);
                    }
                }

                res = cmd.ExecuteScalar();
                conn.Close();
            }

            return res;
        }

        public DbDataReader ExecuteReader(string cmdText, DbParameter[] paramCollection)
        {
            DbDataReader res = null;
            DbConnection conn = CreateConnection();
            conn.Open();
            DbCommand cmd = instance.CreateCommand();
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (paramCollection != null)
            {
                foreach (DbParameter pa in paramCollection)
                {
                    cmd.Parameters.Add(pa);
                }
            }

            res = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return res;
        }

    }

    


}
